package org.example;

import java.sql.*;
import java.util.*;

public class OracleUtil {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:xe";
    private static final String USER = "vocab";
    private static final String PASSWORD = "vocabpass";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 查询某个等级表中出现的单词
    public static Set<String> findWordsInLevel(String tableName, Set<String> words) throws SQLException {
        Set<String> result = new HashSet<>();
        if (words.isEmpty()) return result;
        // 确保所有单词为小写并去重
        List<String> wordList = new ArrayList<>();
        for (String w : words) {
            String lw = w.toLowerCase();
            wordList.add(lw);
            // System.out.println("待查单词定界符:[" + lw + "]");
        }
        int batchSize = 900; // Oracle IN子句限制
        for (int i = 0; i < wordList.size(); i += batchSize) {
            List<String> batch = wordList.subList(i, Math.min(i + batchSize, wordList.size()));
            // System.out.print("本批次参数定界符：");
            // for (String b : batch) {
            //     System.out.print("[" + b + "] ");
            // }
            // System.out.println();
            StringBuilder sql = new StringBuilder("SELECT word FROM " + tableName + " WHERE lower(word) IN (");
            for (int j = 0; j < batch.size(); j++) {
                sql.append("?");
                if (j < batch.size() - 1) sql.append(",");
            }
            sql.append(")");
            // System.out.println("查询表：" + tableName + ", 批次单词数：" + batch.size() + ", SQL: " + sql);
            try (Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sql.toString())) {
                for (int j = 0; j < batch.size(); j++) {
                    ps.setString(j + 1, batch.get(j).toLowerCase());
                }
                ResultSet rs = ps.executeQuery();
                Set<String> batchResult = new HashSet<>();
                while (rs.next()) {
                    batchResult.add(rs.getString(1).toLowerCase());
                }
                // System.out.println("表" + tableName + "本批次命中：" + batchResult);
                result.addAll(batchResult);
            }
        }
        // System.out.println("表" + tableName + "总命中：" + result);
        return result;
    }

    // 统计某个等级表的总单词数
    public static int countWordsInLevel(String tableName) throws SQLException {
        String sql = "SELECT COUNT(*) FROM " + tableName;
        try (Connection conn = getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    public static void main(String[] args) throws Exception {
        // 打印数据库连接信息和当前连接的用户名
        try (Connection conn = getConnection()) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("JDBC URL: " + meta.getURL());
            System.out.println("Database Product: " + meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT user FROM dual");
            if (rs.next()) {
                System.out.println("当前连接用户: " + rs.getString(1));
            }
            // 测试能否读出H表中的第一个单词
            // rs = stmt.executeQuery("SELECT word FROM H WHERE ROWNUM = 1");
            // if (rs.next()) {
            //     System.out.println("H表中的第一个单词: " + rs.getString(1));
            // } else {
            //     System.out.println("H表为空或不存在");
            // }
        }
        // // 简单验证：H表中有'is'单词
        // Set<String> testWords = new HashSet<>();
        // testWords.add("is");
        // Set<String> result = findWordsInLevel("H", testWords);
        // System.out.println("查找结果: " + result);
    }
}